import db from '../config/database.js';
import { uploadToCloudinary, deleteFromCloudinary, getPublicIdFromUrl } from '../utils/cloudinaryUpload.js';

// Get all products with pagination
export const getAllProducts = (req, res) => {
  try {
    const { category_id, is_hot, page = 1, limit = 12 } = req.query;
    const offset = (page - 1) * limit;

    let query = `
      SELECT p.*, c.name as category_name, c.name_en as category_name_en
      FROM products p
      LEFT JOIN categories c ON p.category_id = c.id
      WHERE p.status = 1
    `;
    const params = [];

    if (category_id) {
      query += ' AND p.category_id = ?';
      params.push(category_id);
    }

    if (is_hot) {
      query += ' AND p.is_hot = 1';
    }

    query += ' ORDER BY p.display_order ASC, p.id DESC';
    query += ' LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    const products = db.prepare(query).all(...params);

    // Parse JSON fields
    products.forEach(product => {
      product.images = product.images ? JSON.parse(product.images) : [];
      product.colors = product.colors ? JSON.parse(product.colors) : [];
      product.sizes = product.sizes ? JSON.parse(product.sizes) : [];
    });

    // Get total count
    let countQuery = 'SELECT COUNT(*) as total FROM products WHERE status = 1';
    const countParams = [];
    if (category_id) {
      countQuery += ' AND category_id = ?';
      countParams.push(category_id);
    }
    if (is_hot) {
      countQuery += ' AND is_hot = 1';
    }

    const { total } = db.prepare(countQuery).get(...countParams);

    res.json({
      success: true,
      data: products,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Get product by ID
export const getProductById = (req, res) => {
  try {
    const { id } = req.params;
    const product = db.prepare(`
      SELECT p.*, c.name as category_name, c.name_en as category_name_en
      FROM products p
      LEFT JOIN categories c ON p.category_id = c.id
      WHERE p.id = ?
    `).get(id);

    if (!product) {
      return res.status(404).json({
        success: false,
        message: 'Product not found'
      });
    }

    // Parse JSON fields
    product.images = product.images ? JSON.parse(product.images) : [];
    product.colors = product.colors ? JSON.parse(product.colors) : [];
    product.sizes = product.sizes ? JSON.parse(product.sizes) : [];

    res.json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Create product
export const createProduct = async (req, res) => {
  try {
    const { 
      category_id, name, name_en, description, material, 
      colors, sizes, is_hot, display_order 
    } = req.body;

    // Upload main image
    if (!req.files || !req.files.main_image) {
      return res.status(400).json({
        success: false,
        message: 'Main image is required'
      });
    }

    const mainImageUrl = await uploadToCloudinary(req.files.main_image[0].buffer, 'lucy-fashion/products');

    // Upload additional images
    let imageUrls = [];
    if (req.files.images) {
      const uploadPromises = req.files.images.map(file => 
        uploadToCloudinary(file.buffer, 'lucy-fashion/products')
      );
      imageUrls = await Promise.all(uploadPromises);
    }

    const stmt = db.prepare(`
      INSERT INTO products (
        category_id, name, name_en, description, material,
        colors, sizes, main_image, images, is_hot, display_order
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = stmt.run(
      category_id,
      name,
      name_en,
      description || '',
      material || '',
      colors ? JSON.stringify(JSON.parse(colors)) : '[]',
      sizes ? JSON.stringify(JSON.parse(sizes)) : '[]',
      mainImageUrl,
      JSON.stringify(imageUrls),
      is_hot ? 1 : 0,
      display_order || 0
    );

    res.status(201).json({
      success: true,
      message: 'Product created successfully',
      data: { id: result.lastInsertRowid }
    });
  } catch (error) {
    console.error('Create product error:', error);
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Update product
export const updateProduct = async (req, res) => {
  try {
    const { id } = req.params;
    const { 
      category_id, name, name_en, description, material,
      colors, sizes, is_hot, display_order, status 
    } = req.body;

    // Get existing product
    const existingProduct = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    if (!existingProduct) {
      return res.status(404).json({
        success: false,
        message: 'Product not found'
      });
    }

    let mainImageUrl = existingProduct.main_image;
    let imageUrls = existingProduct.images ? JSON.parse(existingProduct.images) : [];

    // Update main image if provided
    if (req.files && req.files.main_image) {
      mainImageUrl = await uploadToCloudinary(req.files.main_image[0].buffer, 'lucy-fashion/products');
      // Delete old image from Cloudinary
      const oldPublicId = getPublicIdFromUrl(existingProduct.main_image);
      if (oldPublicId) await deleteFromCloudinary(oldPublicId);
    }

    // Update additional images if provided
    if (req.files && req.files.images) {
      const uploadPromises = req.files.images.map(file => 
        uploadToCloudinary(file.buffer, 'lucy-fashion/products')
      );
      const newImageUrls = await Promise.all(uploadPromises);
      imageUrls = [...imageUrls, ...newImageUrls];
    }

    const stmt = db.prepare(`
      UPDATE products 
      SET category_id = ?, name = ?, name_en = ?, description = ?, material = ?,
          colors = ?, sizes = ?, main_image = ?, images = ?, is_hot = ?, 
          display_order = ?, status = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `);

    stmt.run(
      category_id,
      name,
      name_en,
      description,
      material,
      colors ? JSON.stringify(JSON.parse(colors)) : '[]',
      sizes ? JSON.stringify(JSON.parse(sizes)) : '[]',
      mainImageUrl,
      JSON.stringify(imageUrls),
      is_hot ? 1 : 0,
      display_order,
      status !== undefined ? status : 1,
      id
    );

    res.json({
      success: true,
      message: 'Product updated successfully'
    });
  } catch (error) {
    console.error('Update product error:', error);
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Delete product
export const deleteProduct = (req, res) => {
  try {
    const { id } = req.params;

    // Get product to delete images from Cloudinary
    const product = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    if (!product) {
      return res.status(404).json({
        success: false,
        message: 'Product not found'
      });
    }

    // Delete from database
    const stmt = db.prepare('DELETE FROM products WHERE id = ?');
    stmt.run(id);

    // Delete images from Cloudinary (async, don't wait)
    const mainPublicId = getPublicIdFromUrl(product.main_image);
    if (mainPublicId) deleteFromCloudinary(mainPublicId);

    if (product.images) {
      const images = JSON.parse(product.images);
      images.forEach(url => {
        const publicId = getPublicIdFromUrl(url);
        if (publicId) deleteFromCloudinary(publicId);
      });
    }

    res.json({
      success: true,
      message: 'Product deleted successfully'
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Delete product image
export const deleteProductImage = async (req, res) => {
  try {
    const { id } = req.params;
    const { imageUrl } = req.body;

    const product = db.prepare('SELECT images FROM products WHERE id = ?').get(id);
    if (!product) {
      return res.status(404).json({
        success: false,
        message: 'Product not found'
      });
    }

    let images = JSON.parse(product.images || '[]');
    images = images.filter(url => url !== imageUrl);

    const stmt = db.prepare('UPDATE products SET images = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?');
    stmt.run(JSON.stringify(images), id);

    // Delete from Cloudinary
    const publicId = getPublicIdFromUrl(imageUrl);
    if (publicId) await deleteFromCloudinary(publicId);

    res.json({
      success: true,
      message: 'Image deleted successfully'
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

